<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      python/MySQL入门/7-MySQL库操作-表操作 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>python/MySQL入门/7-MySQL库操作-表操作</h2>



  <p class="post-date">2020-10-22</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h1 id="零-库操作"><a href="#零-库操作" class="headerlink" title="零 库操作"></a>零 库操作</h1><h2 id="一-系统数据库"><a href="#一-系统数据库" class="headerlink" title="一 系统数据库"></a>一 系统数据库</h2><p>information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等<br>performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象<br>mysql： 授权库，主要存储系统用户的权限信息<br>test： MySQL数据库系统自动创建的测试数据库</p>
<h2 id="二-创建数据库"><a href="#二-创建数据库" class="headerlink" title="二 创建数据库"></a>二 创建数据库</h2><p>1 语法(help create database)</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE DATABASE 数据库名 charset utf8;</span><br></pre></td></tr></table></figure>

<p>2 数据库命名规则：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">可以由字母、数字、下划线、＠、＃、＄</span><br><span class="line">区分大小写</span><br><span class="line">唯一性</span><br><span class="line">不能使用关键字如 create select</span><br><span class="line">不能单独使用数字</span><br><span class="line">最长128位</span><br></pre></td></tr></table></figure>

<h2 id="三-数据库相关操作"><a href="#三-数据库相关操作" class="headerlink" title="三 数据库相关操作"></a>三 数据库相关操作</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">1 查看数据库</span><br><span class="line">show databases;</span><br><span class="line">show create database db1;</span><br><span class="line">select database();</span><br><span class="line"></span><br><span class="line">2 选择数据库</span><br><span class="line">USE 数据库名</span><br><span class="line"></span><br><span class="line">3 删除数据库</span><br><span class="line">DROP DATABASE 数据库名;</span><br><span class="line"></span><br><span class="line">4 修改数据库</span><br><span class="line">alter database db1 charset utf8;</span><br></pre></td></tr></table></figure>



<h1 id="一、表介绍"><a href="#一、表介绍" class="headerlink" title="一、表介绍"></a>一、表介绍</h1><p>表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段</p>
<p>还记得我们之前写过的‘员工信息表作业’么？存储这员工信息的文件是这样的：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">id,name,age,sex,phone,job</span><br><span class="line"><span class="number">1</span>,Tank,<span class="number">83</span>,female,<span class="number">13651054608</span>,IT</span><br><span class="line"><span class="number">2</span>,lqz,<span class="number">26</span>,male,<span class="number">13304320533</span>,Tearcher</span><br><span class="line"><span class="number">3</span>,Sean,<span class="number">25</span>,male,<span class="number">13332353222</span>,IT</span><br><span class="line"><span class="number">4</span>,Jason,<span class="number">40</span>,male,<span class="number">13332353333</span>,IT</span><br></pre></td></tr></table></figure>

<p>如果把上面这个文件改成一张表,应该是下面这个样子</p>
<table>
<thead>
<tr>
<th align="center">id</th>
<th align="center">name</th>
<th align="center">age</th>
<th align="center">sex</th>
<th align="center">phone</th>
<th align="center">job</th>
</tr>
</thead>
<tbody><tr>
<td align="center">1</td>
<td align="center">Tank</td>
<td align="center">83</td>
<td align="center">female</td>
<td align="center">13651054608</td>
<td align="center">IT</td>
</tr>
<tr>
<td align="center">2</td>
<td align="center">lqz</td>
<td align="center">26</td>
<td align="center">male</td>
<td align="center">13304320533</td>
<td align="center">Tearcher</td>
</tr>
<tr>
<td align="center">3</td>
<td align="center">Sean</td>
<td align="center">25</td>
<td align="center">male</td>
<td align="center">13332353222</td>
<td align="center">IT</td>
</tr>
<tr>
<td align="center">4</td>
<td align="center">Jason</td>
<td align="center">40</td>
<td align="center">male</td>
<td align="center">13332353333</td>
<td align="center">IT</td>
</tr>
</tbody></table>
<p>id,name,age,sex,phone,job称为字段，其余的，一行内容称为一条记录。</p>
<h1 id="二、创建表"><a href="#二、创建表" class="headerlink" title="二、创建表"></a>二、创建表</h1><h2 id="2-1-语法"><a href="#2-1-语法" class="headerlink" title="2.1 语法"></a>2.1 语法</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] );</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<ol>
<li>在同一张表中，字段名是不能相同</li>
<li>宽度和约束条件可选</li>
<li>字段名和类型是必须的</li>
</ol>
<h2 id="2-2-建表"><a href="#2-2-建表" class="headerlink" title="2.2 建表"></a>2.2 建表</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; create database staff;</span><br><span class="line">Query OK, 1 row affected (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; use staff;</span><br><span class="line">Database changed</span><br><span class="line">mysql&gt; create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));</span><br><span class="line">Query OK, 0 rows affected (0.02 sec)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">mysql&gt; show tables;</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line">| Tables_in_staff |</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line">| staff_info      |</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line">row in <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; <span class="keyword">desc</span> staff_info;</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">| id    | int(11)               | YES  |     | NULL    |       |</span><br><span class="line">| name  | varchar(50)           | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(3)                | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum('male','female') | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(11)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(11)           | YES  |     | NULL    |       |</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">rows in <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; <span class="keyword">select</span> <span class="keyword">id</span>,<span class="keyword">name</span>,sex <span class="keyword">from</span> staff_info;</span><br><span class="line">Empty <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; <span class="keyword">select</span> * <span class="keyword">from</span> staff_info;</span><br><span class="line">Empty <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>

<h2 id="2-3-插入数据"><a href="#2-3-插入数据" class="headerlink" title="2.3 插入数据"></a>2.3 插入数据</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; insert into staff_info (id,name,age,sex,phone,job) values (<span class="number">1</span>,<span class="string">'Tank'</span>,<span class="number">83</span>,<span class="string">'female'</span>,<span class="number">13651054608</span>,<span class="string">'IT'</span>);</span><br><span class="line">Query OK, <span class="number">1</span> row affected (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; insert into staff_info values (<span class="number">2</span>,<span class="string">'lqz'</span>,<span class="number">26</span>,<span class="string">'male'</span>,<span class="number">13304320533</span>,<span class="string">'Teacher'</span>);</span><br><span class="line">Query OK, <span class="number">1</span> row affected (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; insert into staff_info values (<span class="number">3</span>,<span class="string">'Sean'</span>,<span class="number">25</span>,<span class="string">'male'</span>,<span class="number">13332353222</span>,<span class="string">'IT'</span>),(<span class="number">4</span>,<span class="string">'Jason'</span>,<span class="number">40</span>,<span class="string">'male'</span>,<span class="number">13332353333</span>,<span class="string">'IT'</span>);</span><br><span class="line">Query OK, <span class="number">2</span> rows affected (<span class="number">0.00</span> sec)</span><br><span class="line">Records: <span class="number">2</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; select * <span class="keyword">from</span> staff_info;</span><br><span class="line">+------+----------+------+--------+-------------+---------+</span><br><span class="line">| id   | name     | age  | sex    | phone       | job     |</span><br><span class="line">+------+----------+------+--------+-------------+---------+</span><br><span class="line">|    <span class="number">1</span> | Tank     |   <span class="number">83</span> | female | <span class="number">13651054608</span> | IT      |</span><br><span class="line">|    <span class="number">2</span> | lqz     |   <span class="number">26</span> | male   | <span class="number">13304320533</span> | Teacher |</span><br><span class="line">|    <span class="number">3</span> | Sean    |   <span class="number">25</span> | male   | <span class="number">13332353222</span> | IT      |</span><br><span class="line">|    <span class="number">4</span> | Jason |   <span class="number">40</span> | male   | <span class="number">13332353333</span> | IT      |</span><br><span class="line">+------+----------+------+--------+-------------+---------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>

<h1 id="三、查看表结构"><a href="#三、查看表结构" class="headerlink" title="三、查看表结构"></a>三、查看表结构</h1><p>查看表结构有两种方式：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">describe [tablename];这种方法和desc [tablename];效果相同；可以查看当前的表结构</span><br></pre></td></tr></table></figure>

<p>虽然desc命令可以查看表的定义，但是其输出的信息还不够全面，为了得到更全面的表定义信息，有时候就需要查看创建表的SQL语句，使用<code>show create table</code>语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。（\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。）</p>
<h2 id="3-1-desc和show-create-table"><a href="#3-1-desc和show-create-table" class="headerlink" title="3.1 desc和show create table"></a>3.1 desc和show create table</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; describe staff_info;</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">| id    | int(11)               | YES  |     | NULL    |       |</span><br><span class="line">| name  | varchar(50)           | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(3)                | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum('male','female') | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(11)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(11)           | YES  |     | NULL    |       |</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">rows in <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; <span class="keyword">desc</span> staff_info;</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">| id    | int(11)               | YES  |     | NULL    |       |</span><br><span class="line">| name  | varchar(50)           | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(3)                | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum('male','female') | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(11)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(11)           | YES  |     | NULL    |       |</span><br><span class="line">+<span class="comment">-------+-----------------------+------+-----+---------+-------+</span></span><br><span class="line">rows in <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; <span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">table</span> staff_info\G;</span><br><span class="line">*************************** 1. row ***************************</span><br><span class="line">       Table: staff_info</span><br><span class="line"><span class="keyword">Create</span> <span class="keyword">Table</span>: <span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`staff_info`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`name`</span> <span class="built_in">varchar</span>(<span class="number">50</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`age`</span> <span class="built_in">int</span>(<span class="number">3</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`sex`</span> enum(<span class="string">'male'</span>,<span class="string">'female'</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`phone`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`job`</span> <span class="built_in">varchar</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span></span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8</span><br><span class="line"><span class="keyword">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.01</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="keyword">ERROR</span>: </span><br><span class="line"><span class="keyword">No</span> <span class="keyword">query</span> specified</span><br></pre></td></tr></table></figure>

<h1 id="四、MySQL支持的数据类型"><a href="#四、MySQL支持的数据类型" class="headerlink" title="四、MySQL支持的数据类型"></a>四、MySQL支持的数据类型</h1><p>MySQL支持的数据类型：[MySQL支持的数据类型]</p>
<h1 id="五、MySQL表的完整性约束"><a href="#五、MySQL表的完整性约束" class="headerlink" title="五、MySQL表的完整性约束"></a>五、MySQL表的完整性约束</h1><p>MySQL表的完整性约束：[MySQL表的完整性约束]</p>
<h1 id="六、修改表结构"><a href="#六、修改表结构" class="headerlink" title="六、修改表结构"></a>六、修改表结构</h1><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">语法：</span><br><span class="line"><span class="number">1.</span> 修改表名</span><br><span class="line">      ALTER TABLE 表名 </span><br><span class="line">                      RENAME 新表名;</span><br><span class="line"></span><br><span class="line"><span class="number">2.</span> 增加字段</span><br><span class="line">      ALTER TABLE 表名</span><br><span class="line">                      ADD 字段名  数据类型 [完整性约束条件…],</span><br><span class="line">                      ADD 字段名  数据类型 [完整性约束条件…];</span><br><span class="line">                            </span><br><span class="line"><span class="number">3.</span> 删除字段</span><br><span class="line">      ALTER TABLE 表名 </span><br><span class="line">                      DROP 字段名;</span><br><span class="line"></span><br><span class="line"><span class="number">4.</span> 修改字段</span><br><span class="line">      ALTER TABLE 表名 </span><br><span class="line">                      MODIFY  字段名 数据类型 [完整性约束条件…];</span><br><span class="line">      ALTER TABLE 表名 </span><br><span class="line">                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];</span><br><span class="line">      ALTER TABLE 表名 </span><br><span class="line">                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];</span><br><span class="line"></span><br><span class="line"><span class="number">5.</span>修改字段排列顺序/在增加的时候指定字段位置</span><br><span class="line">    ALTER TABLE 表名</span><br><span class="line">                     ADD 字段名  数据类型 [完整性约束条件…]  FIRST;</span><br><span class="line">    ALTER TABLE 表名</span><br><span class="line">                     ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;</span><br><span class="line">    ALTER TABLE 表名</span><br><span class="line">                     CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;</span><br><span class="line">    ALTER TABLE 表名</span><br><span class="line">                     MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;</span><br></pre></td></tr></table></figure>

<h2 id="6-1-alter操作非空和唯一（了解）"><a href="#6-1-alter操作非空和唯一（了解）" class="headerlink" title="6.1 alter操作非空和唯一（了解）"></a>6.1 alter操作非空和唯一（了解）</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">create table t(id int unique,name char(<span class="number">10</span>) <span class="keyword">not</span> null);</span><br><span class="line"></span><br><span class="line"><span class="comment">#去掉null约束</span></span><br><span class="line">alter table t modify name char(<span class="number">10</span>) null;</span><br><span class="line"><span class="comment"># 添加null约束</span></span><br><span class="line">alter table t modify name char(<span class="number">10</span>) <span class="keyword">not</span> null;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># 去掉unique约束</span></span><br><span class="line">alter table t drop index id;</span><br><span class="line"><span class="comment"># 添加unique约束</span></span><br><span class="line">alter table t modify id int unique;</span><br><span class="line"></span><br><span class="line">alter处理null和unique约束</span><br></pre></td></tr></table></figure>

<h2 id="6-2-alter操作主键（了解）"><a href="#6-2-alter操作主键（了解）" class="headerlink" title="6.2 alter操作主键（了解）"></a>6.2 alter操作主键（了解）</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">1、首先创建一个数据表table_test：</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> table_test(</span><br><span class="line"><span class="string">`id`</span> <span class="built_in">varchar</span>(<span class="number">100</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line"><span class="string">`name`</span> <span class="built_in">varchar</span>(<span class="number">100</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">PRIMARY <span class="keyword">KEY</span> (<span class="string">`name`</span>)</span><br><span class="line">); </span><br><span class="line">2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。</span><br><span class="line">先删除主键</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> table_test <span class="keyword">drop</span> primary <span class="keyword">key</span>;</span><br><span class="line">然后再增加主键</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> table_test <span class="keyword">add</span> primary <span class="keyword">key</span>(<span class="keyword">id</span>);</span><br><span class="line">注:在增加主键之前,必须先把反复的id删除掉。</span><br></pre></td></tr></table></figure>

<h2 id="6-3-为表添加外键（了解）"><a href="#6-3-为表添加外键（了解）" class="headerlink" title="6.3 为表添加外键（了解）"></a>6.3 为表添加外键（了解）</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">创建press表</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`press`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`name`</span> <span class="built_in">char</span>(<span class="number">10</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) ；</span><br><span class="line"></span><br><span class="line">创建book表</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`book`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`bk_name`</span> <span class="built_in">char</span>(<span class="number">12</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`press_id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="keyword">KEY</span> <span class="string">`press_id`</span> (<span class="string">`press_id`</span>)</span><br><span class="line">) ；</span><br><span class="line"></span><br><span class="line">为book表添加外键</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> book <span class="keyword">add</span> <span class="keyword">constraint</span> fk_id <span class="keyword">foreign</span> <span class="keyword">key</span>(press_id) <span class="keyword">references</span> press(<span class="keyword">id</span>);</span><br><span class="line"></span><br><span class="line">删除外键</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> book <span class="keyword">drop</span> <span class="keyword">foreign</span> <span class="keyword">key</span> fk_id;</span><br></pre></td></tr></table></figure>

<h2 id="6-4-示例"><a href="#6-4-示例" class="headerlink" title="6.4 示例"></a>6.4 示例</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; desc staff_info;</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">11</span>)               | YES  |     | NULL    |       |</span><br><span class="line">| name  | varchar(<span class="number">50</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |       |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 表重命名</span></span><br><span class="line">mysql&gt; alter table staff_info rename staff;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">11</span>)               | YES  |     | NULL    |       |</span><br><span class="line">| name  | varchar(<span class="number">50</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |       |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 删除sex列</span></span><br><span class="line">mysql&gt; alter table staff drop sex;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">0</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type        | Null | Key | Default | Extra |</span><br><span class="line">+-------+-------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">11</span>)     | YES  |     | NULL    |       |</span><br><span class="line">| name  | varchar(<span class="number">50</span>) | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)      | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)  | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>) | YES  |     | NULL    |       |</span><br><span class="line">+-------+-------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.01</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 添加列</span></span><br><span class="line">mysql&gt; alter table staff add sex enum(<span class="string">'male'</span>,<span class="string">'female'</span>);</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0.03</span> sec)</span><br><span class="line">Records: <span class="number">0</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 修改id的宽度</span></span><br><span class="line">mysql&gt; alter table staff modify id int(<span class="number">4</span>);</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">0</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">4</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| name  | varchar(<span class="number">50</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |       |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.01</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 修改name列的字段名</span></span><br><span class="line">mysql&gt; alter table staff change name sname varchar(<span class="number">20</span>);</span><br><span class="line">Query OK, <span class="number">4</span> rows affected (<span class="number">0.03</span> sec)</span><br><span class="line">Records: <span class="number">4</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">4</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| sname | varchar(<span class="number">20</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |       |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 修改sex列的位置</span></span><br><span class="line">mysql&gt; alter table staff modify sex enum(<span class="string">'male'</span>,<span class="string">'female'</span>) after sname;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">0</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">4</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| sname | varchar(<span class="number">20</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |       |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 创建自增id主键</span></span><br><span class="line">mysql&gt; alter table staff modify id int(<span class="number">4</span>) primary key auto_increment;</span><br><span class="line">Query OK, <span class="number">4</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">4</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+----------------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra          |</span><br><span class="line">+-------+-----------------------+------+-----+---------+----------------+</span><br><span class="line">| id    | int(<span class="number">4</span>)                | NO   | PRI | NULL    | auto_increment |</span><br><span class="line">| sname | varchar(<span class="number">20</span>)           | YES  |     | NULL    |                |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |                |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | YES  |     | NULL    |                |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |                |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |                |</span><br><span class="line">+-------+-----------------------+------+-----+---------+----------------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 删除主键，可以看到删除一个自增主键会报错</span></span><br><span class="line">mysql&gt; alter table staff drop primary key;</span><br><span class="line">ERROR <span class="number">1075</span> (<span class="number">42000</span>): Incorrect table definition; there can be only one auto column <span class="keyword">and</span> it must be defined <span class="keyword">as</span> a key</span><br><span class="line"></span><br><span class="line"><span class="comment"># 需要先去掉主键的自增约束，然后再删除主键约束</span></span><br><span class="line">mysql&gt; alter table staff modify id int(<span class="number">11</span>);</span><br><span class="line">Query OK, <span class="number">4</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">4</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">11</span>)               | NO   | PRI | <span class="number">0</span>       |       |</span><br><span class="line">| sname | varchar(<span class="number">20</span>)           | YES  |     | NULL    |       |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | YES  |     | NULL    |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |       |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.01</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; alter table staff drop primary key;</span><br><span class="line">Query OK, <span class="number">4</span> rows affected (<span class="number">0.06</span> sec)</span><br><span class="line">Records: <span class="number">4</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 添加联合主键</span></span><br><span class="line">mysql&gt; alter table staff add primary key (sname,age);</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">0</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 删除主键</span></span><br><span class="line">mysql&gt; alter table staff drop primary key;</span><br><span class="line">Query OK, <span class="number">4</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">4</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 创建主键id</span></span><br><span class="line">mysql&gt; alter table staff add primary key (id);</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">0</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(<span class="number">11</span>)               | NO   | PRI | <span class="number">0</span>       |       |</span><br><span class="line">| sname | varchar(<span class="number">20</span>)           | NO   |     |         |       |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |       |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | NO   |     | <span class="number">0</span>       |       |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |       |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |       |</span><br><span class="line">+-------+-----------------------+------+-----+---------+-------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 为主键添加自增属性</span></span><br><span class="line">mysql&gt; alter table staff modify id int(<span class="number">4</span>) auto_increment;</span><br><span class="line">Query OK, <span class="number">4</span> rows affected (<span class="number">0.02</span> sec)</span><br><span class="line">Records: <span class="number">4</span>  Duplicates: <span class="number">0</span>  Warnings: <span class="number">0</span></span><br><span class="line"></span><br><span class="line">mysql&gt; desc staff;</span><br><span class="line">+-------+-----------------------+------+-----+---------+----------------+</span><br><span class="line">| Field | Type                  | Null | Key | Default | Extra          |</span><br><span class="line">+-------+-----------------------+------+-----+---------+----------------+</span><br><span class="line">| id    | int(<span class="number">4</span>)                | NO   | PRI | NULL    | auto_increment |</span><br><span class="line">| sname | varchar(<span class="number">20</span>)           | NO   |     |         |                |</span><br><span class="line">| sex   | enum(<span class="string">'male'</span>,<span class="string">'female'</span>) | YES  |     | NULL    |                |</span><br><span class="line">| age   | int(<span class="number">3</span>)                | NO   |     | <span class="number">0</span>       |                |</span><br><span class="line">| phone | bigint(<span class="number">11</span>)            | YES  |     | NULL    |                |</span><br><span class="line">| job   | varchar(<span class="number">11</span>)           | YES  |     | NULL    |                |</span><br><span class="line">+-------+-----------------------+------+-----+---------+----------------+</span><br><span class="line">rows <span class="keyword">in</span> set (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>

<h1 id="七、删除表"><a href="#七、删除表" class="headerlink" title="七、删除表"></a>七、删除表</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> 表名;</span><br></pre></td></tr></table></figure>

<h1 id="八、多表结构的分析和创建"><a href="#八、多表结构的分析和创建" class="headerlink" title="八、多表结构的分析和创建"></a>八、多表结构的分析和创建</h1><h2 id="8-1-如何找出两张表之间的关系"><a href="#8-1-如何找出两张表之间的关系" class="headerlink" title="8.1 如何找出两张表之间的关系"></a>8.1 如何找出两张表之间的关系</h2><p>分析步骤：</p>
<ol>
<li>先站在左表的角度去找：是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）</li>
<li>再站在右表的角度去找：是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）</li>
<li>总结：<ol>
<li>多对一：<ul>
<li>如果只有步骤1成立，则是左表多对一右表</li>
<li>如果只有步骤2成立，则是右表多对一左表</li>
</ul>
</li>
<li>多对多<ul>
<li>如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系</li>
</ul>
</li>
<li>一对一:<ul>
<li>如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可</li>
</ul>
</li>
</ol>
</li>
</ol>
<h2 id="8-2-建立表之间的关系——一对多"><a href="#8-2-建立表之间的关系——一对多" class="headerlink" title="8.2 建立表之间的关系——一对多"></a>8.2 建立表之间的关系——一对多</h2><p>一对多或称为多对一</p>
<p>三张表：出版社，作者信息，书</p>
<p>一对多（或多对一）：一个出版社可以出版多本书</p>
<p>关联方式：foreign key</p>
<h3 id="8-2-1-sql示例"><a href="#8-2-1-sql示例" class="headerlink" title="8.2.1 sql示例"></a>8.2.1 sql示例</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">=====================多对一=====================</span><br><span class="line">create table press(</span><br><span class="line">id int primary key auto_increment,</span><br><span class="line">name varchar(<span class="number">20</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table book(</span><br><span class="line">id int primary key auto_increment,</span><br><span class="line">name varchar(<span class="number">20</span>),</span><br><span class="line">press_id int <span class="keyword">not</span> null,</span><br><span class="line">foreign key(press_id) references press(id)</span><br><span class="line">on delete cascade</span><br><span class="line">on update cascade</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">insert into press(name) values</span><br><span class="line">(<span class="string">'北京工业地雷出版社'</span>),</span><br><span class="line">(<span class="string">'人民音乐不好听出版社'</span>),</span><br><span class="line">(<span class="string">'知识产权没有用出版社'</span>)</span><br><span class="line">;</span><br><span class="line"></span><br><span class="line">insert into book(name,press_id) values</span><br><span class="line">(<span class="string">'九阳神功'</span>,<span class="number">1</span>),</span><br><span class="line">(<span class="string">'九阴真经'</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="string">'九阴白骨爪'</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="string">'独孤九剑'</span>,<span class="number">3</span>),</span><br><span class="line">(<span class="string">'降龙十巴掌'</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="string">'葵花宝典'</span>,<span class="number">3</span>)</span><br><span class="line">;</span><br></pre></td></tr></table></figure>

<h3 id="8-2-2-其他示例"><a href="#8-2-2-其他示例" class="headerlink" title="8.2.2 其他示例"></a>8.2.2 其他示例</h3><p>班级和学生：一个班级可以对应多个学生，但一个学生只能对应一个班级</p>
<p>主机和机房：一个机房可以有多台主机，但是一个主机只能属于一个机房</p>
<h2 id="8-3-建立表之间的关系——多对多"><a href="#8-3-建立表之间的关系——多对多" class="headerlink" title="8.3 建立表之间的关系——多对多"></a>8.3 建立表之间的关系——多对多</h2><p>三张表：出版社，作者信息，书</p>
<p>多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多<br>　　<br>关联方式：foreign key+一张新的表</p>
<h3 id="8-3-1-sql示例"><a href="#8-3-1-sql示例" class="headerlink" title="8.3.1 sql示例"></a>8.3.1 sql示例</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br></pre></td><td class="code"><pre><span class="line">=====================多对多=====================</span><br><span class="line">create table author(</span><br><span class="line">id int primary key auto_increment,</span><br><span class="line">name varchar(<span class="number">20</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了</span></span><br><span class="line">create table author2book(</span><br><span class="line">id int <span class="keyword">not</span> null unique auto_increment,</span><br><span class="line">author_id int <span class="keyword">not</span> null,</span><br><span class="line">book_id int <span class="keyword">not</span> null,</span><br><span class="line">constraint fk_author foreign key(author_id) references author(id)</span><br><span class="line">on delete cascade</span><br><span class="line">on update cascade,</span><br><span class="line">constraint fk_book foreign key(book_id) references book(id)</span><br><span class="line">on delete cascade</span><br><span class="line">on update cascade,</span><br><span class="line">primary key(author_id,book_id)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">#插入四个作者，id依次排开</span></span><br><span class="line">insert into author(name) values(<span class="string">'lqz'</span>),(<span class="string">'Tank'</span>),(<span class="string">'Oscar'</span>),(<span class="string">'Mac'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">#每个作者与自己的代表作如下</span></span><br><span class="line">lqz: </span><br><span class="line">九阳神功</span><br><span class="line">九阴真经</span><br><span class="line">九阴白骨爪</span><br><span class="line">独孤九剑</span><br><span class="line">降龙十巴掌</span><br><span class="line">葵花宝典</span><br><span class="line">Tank: </span><br><span class="line">九阳神功</span><br><span class="line">葵花宝典</span><br><span class="line">Oscar:</span><br><span class="line">独孤九剑</span><br><span class="line">降龙十巴掌</span><br><span class="line">葵花宝典</span><br><span class="line">Mac:</span><br><span class="line">九阳神功</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">insert into author2book(author_id,book_id) values</span><br><span class="line">(<span class="number">1</span>,<span class="number">1</span>),</span><br><span class="line">(<span class="number">1</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="number">1</span>,<span class="number">3</span>),</span><br><span class="line">(<span class="number">1</span>,<span class="number">4</span>),</span><br><span class="line">(<span class="number">1</span>,<span class="number">5</span>),</span><br><span class="line">(<span class="number">1</span>,<span class="number">6</span>),</span><br><span class="line">(<span class="number">2</span>,<span class="number">1</span>),</span><br><span class="line">(<span class="number">2</span>,<span class="number">6</span>),</span><br><span class="line">(<span class="number">3</span>,<span class="number">4</span>),</span><br><span class="line">(<span class="number">3</span>,<span class="number">5</span>),</span><br><span class="line">(<span class="number">3</span>,<span class="number">6</span>),</span><br><span class="line">(<span class="number">4</span>,<span class="number">1</span>)</span><br><span class="line">;</span><br></pre></td></tr></table></figure>

<h3 id="8-3-2-其他示例"><a href="#8-3-2-其他示例" class="headerlink" title="8.3.2 其他示例"></a>8.3.2 其他示例</h3><p>服务和机器：一个服务可能被部署到多台机器上，一台机器上也可以部署多个服务</p>
<p>学生和课程：一个学生可以选择多门课程，一门课程也可以被多个学生选择</p>
<h2 id="8-4-建立表之间的关系——一对一"><a href="#8-4-建立表之间的关系——一对一" class="headerlink" title="8.4 建立表之间的关系——一对一"></a>8.4 建立表之间的关系——一对一</h2><p>两张表：学生表和客户表</p>
<p>一对一：一个学生是一个客户</p>
<p>关联方式：foreign key+unique</p>
<h3 id="8-4-1-sql示例"><a href="#8-4-1-sql示例" class="headerlink" title="8.4.1 sql示例"></a>8.4.1 sql示例</h3><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">create table customer(</span><br><span class="line">    -&gt; id int primary key auto_increment,</span><br><span class="line">    -&gt; name varchar(20) not null,</span><br><span class="line">    -&gt; qq varchar(10) not null,</span><br><span class="line">    -&gt; phone char(16) not null</span><br><span class="line">    -&gt; );</span><br><span class="line"></span><br><span class="line">create table student(</span><br><span class="line">    -&gt; id int primary key auto_increment,</span><br><span class="line">    -&gt; class_name varchar(20) not null,</span><br><span class="line">    -&gt; customer_id int unique, #该字段一定要是唯一的</span><br><span class="line">    -&gt; foreign key(customer_id) references customer(id) #外键的字段一定要保证unique</span><br><span class="line">    -&gt; on delete cascade</span><br><span class="line">    -&gt; on update cascade</span><br><span class="line">    -&gt; );</span><br><span class="line"></span><br><span class="line"><span class="comment">#增加客户</span></span><br><span class="line">mysql&gt; insert into customer(name,qq,phone) values</span><br><span class="line">    -&gt; ('韩蕾','31811231',13811341220),</span><br><span class="line">    -&gt; ('杨澜','123123123',15213146809),</span><br><span class="line">    -&gt; ('翁惠天','283818181',1867141331),</span><br><span class="line">    -&gt; ('杨宗河','283818181',1851143312),</span><br><span class="line">    -&gt; ('袁承明','888818181',1861243314),</span><br><span class="line">    -&gt; ('袁清','112312312',18811431230)</span><br><span class="line"></span><br><span class="line">mysql&gt; <span class="comment">#增加学生</span></span><br><span class="line">mysql&gt; insert into student(class_name,customer_id) values</span><br><span class="line">    -&gt; ('脱产1班',3),</span><br><span class="line">    -&gt; ('周末1期',4),</span><br><span class="line">    -&gt; ('周末1期',5)</span><br><span class="line">    -&gt; ;</span><br></pre></td></tr></table></figure>

<h3 id="8-4-2-其他示例"><a href="#8-4-2-其他示例" class="headerlink" title="8.4.2 其他示例"></a>8.4.2 其他示例</h3><p>例一：一个用户只有一个博客</p>
<p>例二：一个管理员唯一对应一个用户</p>
<h1 id="九、作业"><a href="#九、作业" class="headerlink" title="九、作业"></a>九、作业</h1><p>根据表结构合理设计表与表之间的主外键关系和约束，并完成表结构的创建。</p>
<p>[<img src="https://tva1.sinaimg.cn/large/0081Kckwgy1gjzd3bwf73j30ke0dpdj6.jpg" alt="197-MySQL表操作-01.png"></p>
</section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/python/MySQL%E5%85%A5%E9%97%A8/10-MySQL%E8%AE%B0%E5%BD%95%E6%93%8D%E4%BD%9C/">
        <span class="nav-arrow">← </span>
        
          python/MySQL入门/10-MySQL记录操作
        
      </a>
    
    
      <a class="nav-right" href="/python/MySQL%E5%85%A5%E9%97%A8/16-MySQL%E6%80%A7%E8%83%BD%E5%88%86%E6%9E%90%E4%B9%8BExplain/">
        
          python/MySQL入门/16-MySQL性能分析之Explain
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#零-库操作"><span class="toc-nav-text">零 库操作</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#一-系统数据库"><span class="toc-nav-text">一 系统数据库</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#二-创建数据库"><span class="toc-nav-text">二 创建数据库</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#三-数据库相关操作"><span class="toc-nav-text">三 数据库相关操作</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#一、表介绍"><span class="toc-nav-text">一、表介绍</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#二、创建表"><span class="toc-nav-text">二、创建表</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-1-语法"><span class="toc-nav-text">2.1 语法</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-2-建表"><span class="toc-nav-text">2.2 建表</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-3-插入数据"><span class="toc-nav-text">2.3 插入数据</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#三、查看表结构"><span class="toc-nav-text">三、查看表结构</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-1-desc和show-create-table"><span class="toc-nav-text">3.1 desc和show create table</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#四、MySQL支持的数据类型"><span class="toc-nav-text">四、MySQL支持的数据类型</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#五、MySQL表的完整性约束"><span class="toc-nav-text">五、MySQL表的完整性约束</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#六、修改表结构"><span class="toc-nav-text">六、修改表结构</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-1-alter操作非空和唯一（了解）"><span class="toc-nav-text">6.1 alter操作非空和唯一（了解）</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-2-alter操作主键（了解）"><span class="toc-nav-text">6.2 alter操作主键（了解）</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-3-为表添加外键（了解）"><span class="toc-nav-text">6.3 为表添加外键（了解）</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-4-示例"><span class="toc-nav-text">6.4 示例</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#七、删除表"><span class="toc-nav-text">七、删除表</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#八、多表结构的分析和创建"><span class="toc-nav-text">八、多表结构的分析和创建</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#8-1-如何找出两张表之间的关系"><span class="toc-nav-text">8.1 如何找出两张表之间的关系</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#8-2-建立表之间的关系——一对多"><span class="toc-nav-text">8.2 建立表之间的关系——一对多</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#8-2-1-sql示例"><span class="toc-nav-text">8.2.1 sql示例</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#8-2-2-其他示例"><span class="toc-nav-text">8.2.2 其他示例</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#8-3-建立表之间的关系——多对多"><span class="toc-nav-text">8.3 建立表之间的关系——多对多</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#8-3-1-sql示例"><span class="toc-nav-text">8.3.1 sql示例</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#8-3-2-其他示例"><span class="toc-nav-text">8.3.2 其他示例</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#8-4-建立表之间的关系——一对一"><span class="toc-nav-text">8.4 建立表之间的关系——一对一</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#8-4-1-sql示例"><span class="toc-nav-text">8.4.1 sql示例</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#8-4-2-其他示例"><span class="toc-nav-text">8.4.2 其他示例</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#九、作业"><span class="toc-nav-text">九、作业</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/python/MySQL入门/7-MySQL库操作-表操作/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "python/MySQL入门/7-MySQL库操作-表操作",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>